//------------------------------------------------------------------------------
// The contents of this file are subject to the nopCommerce Public License Version 1.0 ("License"); you may not use this file except in compliance with the License.
// You may obtain a copy of the License at  http://www.nopCommerce.com/License.aspx. 
// 
// Software distributed under the License is distributed on an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. 
// See the License for the specific language governing rights and limitations under the License.
// 
// The Original Code is nopCommerce.
// The Initial Developer of the Original Code is NopSolutions.
// All Rights Reserved.
// 
// Contributor(s): _______. 
//------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Text;
using Tathata.Common.Caching;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using System.Globalization;
using Tathata.Common.DAL;
using Tathata.Common.BLL.Entities;
using Tathata.Common.BLL.EntityCollections;

namespace Tathata.Common.DAL.Managers
{
    internal class ProductDBManager
    {
        #region Methods
        public static ProductCollection GetAllProducts(bool showHidden)
        {
            ProductCollection productCollection = new ProductCollection();
            Database db = NopDataHelper.CreateConnection();
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductLoadAll");
            db.AddInParameter(dbCommand, "ShowHidden", DbType.Boolean, showHidden);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Product product = new Product();
                    product.ProductID = NopDataHelper.GetInt(dataReader, "ProductID");
                    product.Name = NopDataHelper.GetString(dataReader, "Name");
                    product.ShortDescription = NopDataHelper.GetString(dataReader, "ShortDescription");
                    product.FullDescription = NopDataHelper.GetString(dataReader, "FullDescription");
                    product.AdminComment = NopDataHelper.GetString(dataReader, "AdminComment");
                    product.ProductTypeID = NopDataHelper.GetInt(dataReader, "ProductTypeID");
                    product.TemplateID = NopDataHelper.GetInt(dataReader, "TemplateID");
                    product.MetaKeywords = NopDataHelper.GetString(dataReader, "MetaKeywords");
                    product.MetaDescription = NopDataHelper.GetString(dataReader, "MetaDescription");
                    product.MetaTitle = NopDataHelper.GetString(dataReader, "MetaTitle");
                    product.SEName = NopDataHelper.GetString(dataReader, "SEName");
                    product.AllowCustomerReviews = NopDataHelper.GetBoolean(dataReader, "AllowCustomerReviews");
                    product.RatingSum = NopDataHelper.GetInt(dataReader, "RatingSum");
                    product.TotalRatingVotes = NopDataHelper.GetInt(dataReader, "TotalRatingVotes");
                    product.Published = NopDataHelper.GetBoolean(dataReader, "Published");
                    product.Deleted = NopDataHelper.GetBoolean(dataReader, "Deleted");
                    product.CreatedOn = NopDataHelper.GetDateTime(dataReader, "CreatedOn");
                    product.UpdatedOn = NopDataHelper.GetDateTime(dataReader, "UpdatedOn");
                    productCollection.Add(product);
                }
            }

            return productCollection;
        }

        public static Product GetByProductID(int ProductID)
        {
            Product product = null;
            if (ProductID == 0)
                return product;
            Database db = NopDataHelper.CreateConnection();
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductLoadByPrimaryKey");
            db.AddInParameter(dbCommand, "ProductID", DbType.Int32, ProductID);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    product = new Product();
                    product.ProductID = NopDataHelper.GetInt(dataReader, "ProductID");
                    product.Name = NopDataHelper.GetString(dataReader, "Name");
                    product.ShortDescription = NopDataHelper.GetString(dataReader, "ShortDescription");
                    product.FullDescription = NopDataHelper.GetString(dataReader, "FullDescription");
                    product.AdminComment = NopDataHelper.GetString(dataReader, "AdminComment");
                    product.ProductTypeID = NopDataHelper.GetInt(dataReader, "ProductTypeID");
                    product.TemplateID = NopDataHelper.GetInt(dataReader, "TemplateID");
                    product.MetaKeywords = NopDataHelper.GetString(dataReader, "MetaKeywords");
                    product.MetaDescription = NopDataHelper.GetString(dataReader, "MetaDescription");
                    product.MetaTitle = NopDataHelper.GetString(dataReader, "MetaTitle");
                    product.SEName = NopDataHelper.GetString(dataReader, "SEName");
                    product.AllowCustomerReviews = NopDataHelper.GetBoolean(dataReader, "AllowCustomerReviews");
                    product.RatingSum = NopDataHelper.GetInt(dataReader, "RatingSum");
                    product.TotalRatingVotes = NopDataHelper.GetInt(dataReader, "TotalRatingVotes");
                    product.Published = NopDataHelper.GetBoolean(dataReader, "Published");
                    product.Deleted = NopDataHelper.GetBoolean(dataReader, "Deleted");
                    product.CreatedOn = NopDataHelper.GetDateTime(dataReader, "CreatedOn");
                    product.UpdatedOn = NopDataHelper.GetDateTime(dataReader, "UpdatedOn");
                }
            }
            return product;
        }

        public static Product InsertProduct(string Name, string ShortDescription, string FullDescription, string AdminComment,
            int ProductTypeID, int TemplateID, string MetaKeywords, string MetaDescription, string MetaTitle,
            string SEName, bool AllowCustomerReviews, int RatingSum, int TotalRatingVotes, bool Published, bool Deleted, DateTime CreatedOn, DateTime UpdatedOn)
        {
            Product product = null;
            Database db = NopDataHelper.CreateConnection();
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductInsert");
            db.AddOutParameter(dbCommand, "ProductID", DbType.Int32, 0);
            db.AddInParameter(dbCommand, "Name", DbType.String, Name);
            db.AddInParameter(dbCommand, "ShortDescription", DbType.String, ShortDescription);
            db.AddInParameter(dbCommand, "FullDescription", DbType.String, FullDescription);
            db.AddInParameter(dbCommand, "AdminComment", DbType.String, AdminComment);
            db.AddInParameter(dbCommand, "ProductTypeID", DbType.Int32, ProductTypeID);
            db.AddInParameter(dbCommand, "TemplateID", DbType.Int32, TemplateID);
            db.AddInParameter(dbCommand, "MetaKeywords", DbType.String, MetaKeywords);
            db.AddInParameter(dbCommand, "MetaDescription", DbType.String, MetaDescription);
            db.AddInParameter(dbCommand, "MetaTitle", DbType.String, MetaTitle);
            db.AddInParameter(dbCommand, "SEName", DbType.String, SEName);
            db.AddInParameter(dbCommand, "AllowCustomerReviews", DbType.Boolean, AllowCustomerReviews);
            db.AddInParameter(dbCommand, "RatingSum", DbType.Int32, RatingSum);
            db.AddInParameter(dbCommand, "TotalRatingVotes", DbType.Int32, TotalRatingVotes);
            db.AddInParameter(dbCommand, "Published", DbType.Boolean, Published);
            db.AddInParameter(dbCommand, "Deleted", DbType.Boolean, Deleted);
            db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, CreatedOn);
            db.AddInParameter(dbCommand, "UpdatedOn", DbType.DateTime, UpdatedOn);
            if (db.ExecuteNonQuery(dbCommand) > 0)
            {
                int ProductID = Convert.ToInt32(db.GetParameterValue(dbCommand, "@ProductID"));
                product = GetByProductID(ProductID);
            }

            return product;
        }

        public static Product UpdateProduct(int ProductID, string Name, string ShortDescription, string FullDescription, string AdminComment,
            int ProductTypeID, int TemplateID, string MetaKeywords, string MetaDescription, string MetaTitle,
            string SEName, bool AllowCustomerReviews, int RatingSum, int TotalRatingVotes, bool Published, bool Deleted, DateTime CreatedOn, DateTime UpdatedOn)
        {
            Product product = null;
            Database db = NopDataHelper.CreateConnection();
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductUpdate");
            db.AddInParameter(dbCommand, "ProductID", DbType.Int32, ProductID);
            db.AddInParameter(dbCommand, "Name", DbType.String, Name);
            db.AddInParameter(dbCommand, "ShortDescription", DbType.String, ShortDescription);
            db.AddInParameter(dbCommand, "FullDescription", DbType.String, FullDescription);
            db.AddInParameter(dbCommand, "AdminComment", DbType.String, AdminComment);
            db.AddInParameter(dbCommand, "ProductTypeID", DbType.Int32, ProductTypeID);
            db.AddInParameter(dbCommand, "TemplateID", DbType.Int32, TemplateID);
            db.AddInParameter(dbCommand, "MetaKeywords", DbType.String, MetaKeywords);
            db.AddInParameter(dbCommand, "MetaDescription", DbType.String, MetaDescription);
            db.AddInParameter(dbCommand, "MetaTitle", DbType.String, MetaTitle);
            db.AddInParameter(dbCommand, "SEName", DbType.String, SEName);
            db.AddInParameter(dbCommand, "AllowCustomerReviews", DbType.Boolean, AllowCustomerReviews);
            db.AddInParameter(dbCommand, "RatingSum", DbType.Int32, RatingSum);
            db.AddInParameter(dbCommand, "TotalRatingVotes", DbType.Int32, TotalRatingVotes);
            db.AddInParameter(dbCommand, "Published", DbType.Boolean, Published);
            db.AddInParameter(dbCommand, "Deleted", DbType.Boolean, Deleted);
            db.AddInParameter(dbCommand, "CreatedOn", DbType.DateTime, CreatedOn);
            db.AddInParameter(dbCommand, "UpdatedOn", DbType.DateTime, UpdatedOn);
            if (db.ExecuteNonQuery(dbCommand) > 0)
                product = GetByProductID(ProductID);

            return product;
        }

        public static void SetProductRating(int ProductID, int CustomerID, int Rating, DateTime RatedOn)
        {
            Database db = NopDataHelper.CreateConnection();
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductRatingCreate");
            db.AddInParameter(dbCommand, "ProductID", DbType.Int32, ProductID);
            db.AddInParameter(dbCommand, "CustomerID", DbType.Int32, CustomerID);
            db.AddInParameter(dbCommand, "Rating", DbType.Int32, Rating);
            db.AddInParameter(dbCommand, "RatedOn", DbType.DateTime, RatedOn);
            db.ExecuteNonQuery(dbCommand);
        }

        public static ProductCollection Search(string Keywords, bool SearchDescriptions,
           int PageNumber, int PageSize, bool showHidden, out int TotalProducts)
        {
            ProductCollection productCollection = new ProductCollection();
            Database db = NopDataHelper.CreateConnection();
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductSearch");
            db.AddInParameter(dbCommand, "Keywords", DbType.String, Keywords);
            db.AddInParameter(dbCommand, "SearchDescriptions", DbType.Boolean, SearchDescriptions);
            db.AddInParameter(dbCommand, "PageNumber", DbType.Int32, PageNumber);
            db.AddInParameter(dbCommand, "PageSize", DbType.Int32, PageSize);
            db.AddInParameter(dbCommand, "ShowHidden", DbType.Boolean, showHidden);
            db.AddOutParameter(dbCommand, "TotalProducts", DbType.Int32, 0);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Product product = new Product();
                    product.ProductID = NopDataHelper.GetInt(dataReader, "ProductID");
                    product.Name = NopDataHelper.GetString(dataReader, "Name");
                    product.ShortDescription = NopDataHelper.GetString(dataReader, "ShortDescription");
                    product.FullDescription = NopDataHelper.GetString(dataReader, "FullDescription");
                    product.AdminComment = NopDataHelper.GetString(dataReader, "AdminComment");
                    product.ProductTypeID = NopDataHelper.GetInt(dataReader, "ProductTypeID");
                    product.TemplateID = NopDataHelper.GetInt(dataReader, "TemplateID");
                    product.MetaKeywords = NopDataHelper.GetString(dataReader, "MetaKeywords");
                    product.MetaDescription = NopDataHelper.GetString(dataReader, "MetaDescription");
                    product.MetaTitle = NopDataHelper.GetString(dataReader, "MetaTitle");
                    product.SEName = NopDataHelper.GetString(dataReader, "SEName");
                    product.AllowCustomerReviews = NopDataHelper.GetBoolean(dataReader, "AllowCustomerReviews");
                    product.RatingSum = NopDataHelper.GetInt(dataReader, "RatingSum");
                    product.TotalRatingVotes = NopDataHelper.GetInt(dataReader, "TotalRatingVotes");
                    product.Published = NopDataHelper.GetBoolean(dataReader, "Published");
                    product.Deleted = NopDataHelper.GetBoolean(dataReader, "Deleted");
                    product.CreatedOn = NopDataHelper.GetDateTime(dataReader, "CreatedOn");
                    product.UpdatedOn = NopDataHelper.GetDateTime(dataReader, "UpdatedOn");
                    productCollection.Add(product);
                }
                TotalProducts = Convert.ToInt32(db.GetParameterValue(dbCommand, "@TotalProducts"));
            }

            return productCollection;
        }

        public static ProductCollection GetRecentlyAddedProducts(int Number, bool showHidden)
        {
            ProductCollection productCollection = new ProductCollection();
            Database db = NopDataHelper.CreateConnection();
            DbCommand dbCommand = db.GetStoredProcCommand("Nop_ProductLoadRecentlyAdded");
            db.AddInParameter(dbCommand, "Number", DbType.Int32, Number);
            db.AddInParameter(dbCommand, "ShowHidden", DbType.Boolean, showHidden);
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    Product product = new Product();
                    product.ProductID = NopDataHelper.GetInt(dataReader, "ProductID");
                    product.Name = NopDataHelper.GetString(dataReader, "Name");
                    product.ShortDescription = NopDataHelper.GetString(dataReader, "ShortDescription");
                    product.FullDescription = NopDataHelper.GetString(dataReader, "FullDescription");
                    product.AdminComment = NopDataHelper.GetString(dataReader, "AdminComment");
                    product.ProductTypeID = NopDataHelper.GetInt(dataReader, "ProductTypeID");
                    product.TemplateID = NopDataHelper.GetInt(dataReader, "TemplateID");
                    product.MetaKeywords = NopDataHelper.GetString(dataReader, "MetaKeywords");
                    product.MetaDescription = NopDataHelper.GetString(dataReader, "MetaDescription");
                    product.MetaTitle = NopDataHelper.GetString(dataReader, "MetaTitle");
                    product.SEName = NopDataHelper.GetString(dataReader, "SEName");
                    product.AllowCustomerReviews = NopDataHelper.GetBoolean(dataReader, "AllowCustomerReviews");
                    product.RatingSum = NopDataHelper.GetInt(dataReader, "RatingSum");
                    product.TotalRatingVotes = NopDataHelper.GetInt(dataReader, "TotalRatingVotes");
                    product.Published = NopDataHelper.GetBoolean(dataReader, "Published");
                    product.Deleted = NopDataHelper.GetBoolean(dataReader, "Deleted");
                    product.CreatedOn = NopDataHelper.GetDateTime(dataReader, "CreatedOn");
                    product.UpdatedOn = NopDataHelper.GetDateTime(dataReader, "UpdatedOn");
                    productCollection.Add(product);
                }
            }

            return productCollection;
        }
        #endregion
    }
}
